﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Maticsoft.DBUtility;
using Aspose.Words;
using System.Data;
using System.Data.OracleClient;
using System.IO;
using System.Text;
using System.Collections.Specialized;
using System.Configuration;
using Aspose.Words.Rendering;
using Aspose.Words.Drawing;
using DQYT.HTDZ.Web;
using DQYT.HTDZ.Common;
using System.Web.Services;
using Microsoft.JScript;
//using Microsoft.Office.Interop.Word;

namespace SkWeb.FAXZ
{
    public partial class WordBrowse : System.Web.UI.Page
    {
        Aspose.Words.Document tempDocument;
       
        protected void Page_Load(object sender, EventArgs e)
        {
            //string strTemplate_id = Request.QueryString["tempalteid"];//模板ID
            //string strJing_id = Request.QueryString["id"];//模板ID
            string wd = Request.QueryString["wd"];

            string op = Request.QueryString["op"];
            if (op == null)
                return;
          

            switch (op)
            {
                case "saveWordDoc"://保存并添加新方案时调用，把井id、模板id还有修改的word模板保存到数据库
                    //insertNewSchemeToFtp();
                    insertNewSchemeToOracle();
                    break;
                case  "browsefromDB": //从方案管理页面浏览时，传入井号和方案名
                    browseFromDB();
                    //browseFromFtp();
                    break;
                case "browseFromLFS"://当方案没有生成时，模板从服务器文件系统加载， ---直接加载模板
                    browseFromLFS();
                    break;
                case "download"://下载word方案时调用，传入井号和方案名
                    downloadDoc();
                    break;
                case "sh":
                    browseSh();//审核方案时调用
                    break;
                case"getBFL":
                    GetBfl(wd);
                    break;
               
            }

        }
        [WebMethod]
        public static string GetBfl(string wd)
        {
            //百分率
            wd = Math.Ceiling(System.Convert.ToDouble(wd)).ToString();
            string strPerSql = "select tem_value from tb_temperature where tem= " + wd;//+ " and TEM_TYPE=213";
            float percent = System.Convert.ToSingle(OracleHelper.Query(strPerSql).Tables[0].Rows[0][0]);
            return percent.ToString();
        }
        [WebMethod]
        public static string GetBfl1(string wd)
        {
            //百分率
            wd = Math.Ceiling(System.Convert.ToDouble(wd)).ToString();
            string strPerSql = "select tem_value from tb_temperature where tem= " + wd + " and TEM_TYPE=692";
            float percent = System.Convert.ToSingle(OracleHelper.Query(strPerSql).Tables[0].Rows[0][0]);
            return percent.ToString();
        }
        [WebMethod]
        public static string GetFJ(string tmp_id)
        {
            string fj = "";
            string TMP_MC = "";
            string strSql = "select * from tb_template_menu where tmp_id =" + tmp_id;
            TMP_MC = OracleHelper.Query(strSql).Tables[0].Rows[0]["TMP_MC"].ToString();
            if (TMP_MC.IndexOf("压力2级") != -1 || TMP_MC.IndexOf("压力3级") != -1 || TMP_MC.IndexOf("压力4级") != -1 || TMP_MC.IndexOf("压力5级") != -1 || TMP_MC.IndexOf("压力6级") != -1)
            {
                fj = System.Text.RegularExpressions.Regex.Replace(TMP_MC, @"[^\d{2}-]*", "");
            }
            string TMP_URL = OracleHelper.Query(strSql).Tables[0].Rows[0]["TMP_URL"].ToString();
            if (!string.IsNullOrEmpty(TMP_URL))
            {
                if (TMP_URL.IndexOf("压力+压力开孔") != -1 && TMP_URL.IndexOf("（30-60）.doc") != -1)
                {
                    fj = "压力+压力开孔30-60";
                }
                else if (TMP_URL.IndexOf("压力+压力开孔") != -1 && TMP_URL.IndexOf("（60-120）.doc") != -1)
                {
                    fj = "压力+压力开孔60-120";
                }
                else if (TMP_URL.IndexOf("压力+压力开孔") != -1 && TMP_URL.IndexOf("（60-120）+（30-60）.doc") != -1)
                {
                    fj = "压力+压力开孔（60-120）+（30-60）";
                }
                else if (TMP_URL.IndexOf("压力+压力开孔") != -1 && TMP_URL.IndexOf("（30+60）+（30-60）+（30-60）.doc") != -1)
                {
                    fj = "压力+压力开孔（30+60）+（30-60）+（30-60）";
                }
                else if (TMP_URL.IndexOf("射孔-下泵联作") != -1 && TMP_URL.IndexOf("新井不分级.doc") != -1)
                {
                    fj = "射孔-下泵联作新井不分级";
                }
                
            }

            return fj;
        }


        private void browseSh()
        {
            string strFaId = Request.QueryString["id"].Trim();//设计完成之后的方案id
            string strStep = Request.QueryString["step"];
            string csfs =   Request.QueryString["csfs"];
            string tjyy= Request.QueryString["tjyy"];
            string strSql = "select jing_id,tmp_id from tb_fa where id="+strFaId;
            System.Data.DataTable dt = OracleHelper.Query(strSql).Tables[0];

            string strJing_id = dt.Rows[0]["JING_ID"].ToString().Trim();//井名
            string strTemplate_id = dt.Rows[0]["TMP_ID"].ToString().Trim();//模板名

            MemoryStream ms = ReadBlob(System.Convert.ToInt32(strFaId), "id", "word_doc", "tb_fa");

           // Microsoft.Office.Interop.Word.Document document = new Microsoft.Office.Interop.Word.Document(
            tempDocument = new Aspose.Words.Document(ms);
            
            DocumentBuilder builder = new DocumentBuilder(tempDocument);
            string bookMarkName = "";
            string imgDir = Server.MapPath("~/DesignSchemeTempalte/签名/");
            string imgPath="";
            if (strStep == "first")
            {
                  //  HeaderEven = 0,
       // HeaderPrimary = 1,
       // FooterEven = 2,
       // FooterPrimary = 3,
       // HeaderFirst = 4,
      //  FooterFirst = 5,

                builder.MoveToHeaderFooter(HeaderFooterType.HeaderPrimary);
                builder.Write("Header First");



                bookMarkName = "first";
                builder.MoveToBookmark(bookMarkName);
                imgPath = imgDir + DQYT.HTDZ.Web.Globals.CurrentUserInfo.USE_NAME;
                if (File.Exists(imgPath + ".jpg"))
                    imgPath += ".jpg";
                else
                    imgPath += ".png";
                //tempDocument.Range.Bookmarks["初审时间"].Text = DateTime.Now.Year.ToString() + "年" + DateTime.Now.Month.ToString() + "月" + DateTime.Now.Day.ToString() + "日";
                builder.InsertImage(imgPath);
            }
            else if (strStep == "second")
            {

                try
                {
                    tempDocument.Range.Bookmarks["初审日期"].Text = DateTime.Now.Year.ToString() + "年" + DateTime.Now.Month.ToString() + "月" + DateTime.Now.Day.ToString() + "日";
                }
                catch (Exception)
                { }
                bookMarkName = "second";
                builder.MoveToBookmark(bookMarkName);
                imgPath = imgDir + DQYT.HTDZ.Web.Globals.CurrentUserInfo.USE_NAME;
                if (File.Exists(imgPath + ".jpg"))
                {
                    imgPath += ".jpg";
                }
                else
                {
                    imgPath += ".png";
                }

                builder.InsertImage(imgPath);

                if (csfs.Contains("电缆"))
                {

                    bookMarkName = "third";
                    builder.MoveToBookmark(bookMarkName);
                    imgPath = imgDir +"石莹";
                    if (File.Exists(imgPath + ".jpg"))
                    {
                        imgPath += ".jpg";
                    }
                    else
                    {
                        imgPath += ".png";
                    }

                    builder.InsertImage(imgPath);
                     
                    bookMarkName = "fourth";
                    builder.MoveToBookmark(bookMarkName);
                    imgPath = imgDir + "刘桥";
                    if (File.Exists(imgPath + ".jpg"))
                    {
                        imgPath += ".jpg";
                    }
                    else
                    {
                        imgPath += ".png";
                    }

                    builder.InsertImage(imgPath);
                }
               

            }
            else if (strStep == "third")
            {
                try
                {
                    tempDocument.Range.Bookmarks["复审日期"].Text = DateTime.Now.Year.ToString() + "年" + DateTime.Now.Month.ToString() + "月" + DateTime.Now.Day.ToString() + "日";
                    tempDocument.Range.Bookmarks["审批日期"].Text = DateTime.Now.Year.ToString() + "年" + DateTime.Now.Month.ToString() + "月" + DateTime.Now.Day.ToString() + "日";
                }
                catch (Exception ex)
                { }
                bookMarkName = "third";
                builder.MoveToBookmark(bookMarkName);
                imgPath = imgDir + DQYT.HTDZ.Web.Globals.CurrentUserInfo.USE_NAME;
                if (File.Exists(imgPath + ".jpg"))
                {
                    imgPath += ".jpg";
                }
                else
                {
                    imgPath += ".png";
                }
                builder.InsertImage(imgPath);

                if (csfs.Contains("油管"))
                {
     
                    bookMarkName = "fourth";
                    builder.MoveToBookmark(bookMarkName);
                    imgPath = imgDir + "刘桥";
                    if (File.Exists(imgPath + ".jpg"))
                    {
                        imgPath += ".jpg";
                    }
                    else
                    {
                        imgPath += ".png";
                    }
                    builder.InsertImage(imgPath);


                }
                else if (csfs.Contains("作业"))
                {
                    bookMarkName = "fourth";
                    builder.MoveToBookmark(bookMarkName);
                    imgPath = imgDir + "石莹";
                    if (File.Exists(imgPath + ".jpg"))
                    {
                        imgPath += ".jpg";
                    }
                    else
                    {
                        imgPath += ".png";
                    }
                    builder.InsertImage(imgPath);
                }
            }

            

            string docPath= Server.MapPath("../DOC");
            //docPath=Path.Combine(docPath,strJing_id+strTemplate_id+".doc");

            docPath = Path.Combine(docPath, "temp" + ".doc");
            tempDocument.Save(docPath);
            string strIsShowDiv = "";
            if (Request.QueryString["hide_div_tool"] == "1")
            {
                strIsShowDiv="&hide_div_tool=1";
            }

            if (tjyy == "1")
            {
                //Response.Redirect("WordBrowse.aspx?tempalteid=" + strTemplate_id + "&id=" + strJing_id + "&path=" + Server.UrlEncode("Doc/"+ strJing_id + strTemplate_id + ".doc"));
                Response.Redirect("WordBrowse.aspx?tjyy=1&tempalteid=" + strTemplate_id + "&id=" + strJing_id + "&path=" + Server.UrlEncode("Doc/temp.doc") + strIsShowDiv);

            }
            else
            {
                //Response.Redirect("WordBrowse.aspx?tempalteid=" + strTemplate_id + "&id=" + strJing_id + "&path=" + Server.UrlEncode("Doc/"+ strJing_id + strTemplate_id + ".doc"));
                Response.Redirect("WordBrowse.aspx?tempalteid=" + strTemplate_id + "&id=" + strJing_id + "&path=" + Server.UrlEncode("Doc/temp.doc") + strIsShowDiv);

            }
            return;
        }

       
        private void downloadDoc()
        {

            string strFaId = Request.QueryString["id"].Trim();//设计完成之后的方案id
            string strSql = "select jing_id,tmp_id from tb_fa where id=" + strFaId;
           System.Data.DataTable dt = OracleHelper.Query(strSql).Tables[0];

            string strJing_id = dt.Rows[0]["JING_ID"].ToString().Trim();//井名
            string strTemplate_id = dt.Rows[0]["TMP_ID"].ToString().Trim();//模板名

            strSql = "select jh from skgysjb t where id = " + strJing_id;
            string strJh = "temp";
            
            
            dt =   OracleHelper.Query(strSql).Tables[0];//
            if (dt.Rows.Count > 0)
            {
                strJh=dt.Rows[0]["JH"].ToString();
            }


            MemoryStream ms = ReadBlob(System.Convert.ToInt32(strFaId), "id", "word_doc", "tb_fa");
            Aspose.Words.Document tempDocument = new Aspose.Words.Document(ms);
            tempDocument.Protect(ProtectionType.ReadOnly);
            //string strFileName = genWordDoc(strJing_id, strTemplate_id, tempDocument);
            string docPath = Server.MapPath("../DOC");
            docPath = Path.Combine(docPath, strJh + ".doc");
            tempDocument.Save(docPath);

            Response.Redirect("../Doc/" + strJh + ".doc");
           // Response.Redirect("WordBrowse.aspx?tempalteid=" + strTemplate_id + "&id=" + strJing_id + "&path=" + Server.UrlEncode("Doc/" + strJing_id + strTemplate_id + ".doc"));
            return;
            //string strJingName = Request.QueryString["jingName"].Trim();//井名
            //string strTmpName = Request.QueryString["tmpName"].Trim();//模板名

            //string strSql = "select id from skgysjb where trim(jh)='" + strJingName + "'";
            //string strJing_id = OracleHelper.Query(strSql).Tables[0].Rows[0][0].ToString();

            //strSql = "select tmp_id from tb_template_menu where tmp_mc='" + strTmpName + "'";
            //string strTemplate_id = OracleHelper.Query(strSql).Tables[0].Rows[0][0].ToString();

            //FtpWeb ftp = new FtpWeb();
            //string errorMsg = "";
            //ftp.Download("skdoc", strJing_id + '_' + strTemplate_id + ".doc", out errorMsg);
            //MemoryStream ms = ReadBlob(Convert.ToInt32(strJing_id), "jing_id", "word_doc", "tb_fa");
            //Document tempDocument = new Aspose.Words.Document(ms);
            //string strPath = Server.MapPath("~/tempDoc/") +genWordDoc(strJing_id, strTemplate_id, tempDocument);
            //Response.ContentType = "application/msword";
            //Response.AddHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode(strJingName) + "_" + Server.UrlEncode(strTmpName) + ".doc");
            //Response.TransmitFile(strPath);
            
           
        }

        /// <summary>
        /// 从数据库加载模板生成文档
        /// </summary>
        private void browseFromDB()
        {
            //string strJingName = Request.QueryString["jingName"].Trim();//井名
            //string strTmpName = Request.QueryString["tmpName"].Trim();//模板名
            //string strSql ="select word_doc from tb_fa where jing_id= "+ +"tmp_id="+

            /*
            string strJingName = Request.QueryString["jingName"].Trim();//井名
            string strTmpName = Request.QueryString["tmpName"].Trim();//模板名

            string strSql = "select id from skgysjb where trim(jh)='" + strJingName + "'";
            string strJing_id = OracleHelper.Query(strSql).Tables[0].Rows[0][0].ToString();
            
            strSql = "select tmp_id from tb_template_menu where tmp_mc='" + strTmpName + "'";
            string strTemplate_id = OracleHelper.Query(strSql).Tables[0].Rows[0][0].ToString();

            MemoryStream ms = ReadBlob(Convert.ToInt32(strJing_id), "jing_id", "word_doc", "tb_fa");
            Document  tempDocument = new Aspose.Words.Document(ms);
           
            string strFileName = genWordDoc(strJing_id, strTemplate_id,tempDocument);
            Response.Redirect("WordBrowse.aspx?tempalteid=" + strTemplate_id + "&id=" + strJing_id + "&name=" + Server.UrlEncode(strFileName));
            return;
             */
        }


        /// <summary>
        /// 获取文件在ftp服务器上的所在路径，并传到前台
        /// </summary>
        private void browseFromFtp()
        {
            string strTemplate_id = Request.QueryString["tempalteid"];//模板ID
            string strJing_id = Request.QueryString["id"];//模板ID
            string strPath = FtpWeb.path + strJing_id + '_' + strTemplate_id + ".doc";
            Response.Redirect("WordBrowse.aspx?tempalteid=" + strTemplate_id + "&id=" + strJing_id + "&path=" + Server.UrlEncode(strPath) );
            return;
        }

        /// <summary>
        /// 从头本地文件系统提取模板生成文档
        /// </summary>
        private void browseFromLFS()
        {
            string strTemplate_id = Request.QueryString["tempalteid"];//模板ID
            string strJing_id = Request.QueryString["id"];//模板ID
            string strData = Request.QueryString["data"];
            string cs = Request.QueryString["cs"];
            string strSql = "select tmp_url from tb_template_menu where tmp_id =" + strTemplate_id;
            string docPath = OracleHelper.Query(strSql).Tables[0].Rows[0][0].ToString();

            string tmpPath = Server.MapPath("~/"+docPath);
            Document document = new Document(tmpPath);


            DocumentBuilder builder = new DocumentBuilder(document);
            string bookMarkName = "";
            string imgDir = Server.MapPath("~/DesignSchemeTempalte/签名/");
            bookMarkName = "first";
            builder.MoveToBookmark(bookMarkName);
            string imgPath = imgDir + DQYT.HTDZ.Web.Globals.CurrentUserInfo.USE_NAME;
            if (File.Exists(imgPath + ".jpg"))
                 imgPath += ".jpg";
             else
                 imgPath += ".png";

            builder.InsertImage(imgPath);
            document.Save(Server.MapPath("~/tmp.doc"));


            docPath = "tmp.doc";

            Response.Cookies["bookMarkAndData"].Value = Microsoft.JScript.GlobalObject.escape(strData);
            //strData = strData.Replace("审核日期1", "");
            //docPath = Server.MapPath("~/") + docPath;
            ///服务器端生成word文档并把路径传递到前台
            //Aspose.Words.Document tempDocument;
            //tempDocument = new Aspose.Words.Document(docPath);
            //string strFileName = genWordDoc(strJing_id, strTemplate_id, tempDocument);
            docPath=docPath.Replace('\\','/');
            Response.Redirect("WordBrowse.aspx?tempalteid=" + strTemplate_id + "&id=" + strJing_id + "&name=" + Server.UrlEncode(docPath)  + "&cs=" + cs);
            //Response.Redirect("WordBrowse.aspx?tempalteid=" + strTemplate_id + "&id=" + strJing_id + "&name=" + Server.UrlEncode(docPath) + "&data=" + strData + "&cs=" + cs);
            return;

        }
        /// <summary>
        /// 生成word文档方法
        /// </summary>
        /// <param name="strJingId">井基础数据的ID好</param>
        /// <param name="strTemplateId">模板ID编号</param>
        /// <param name="tempDocument"></param>
        /// <returns></returns>
        public string genWordDoc(string strJingId, string strTemplateId,Aspose.Words.Document tempDocument)
        {
            Aspose.Words.DocumentBuilder builder;
            builder = new Aspose.Words.DocumentBuilder(tempDocument);
            string strSql = "select * from tb_bookmark1 ";//where faid =" + strTemplateId;
            System.Data.DataTable dtBM = OracleHelper.Query(strSql).Tables[0];
            DataColumn dc = new DataColumn("VALUE", Type.GetType("System.String"));
            dtBM.Columns.Add(dc);
            FillTable(dtBM, strJingId);

            foreach (DataRow dr in dtBM.Rows)
            {
                tempDocument.Range.Bookmarks[dr["NAME"].ToString()].Text = dr["Value"].ToString().Trim();
            }

            ////下面的代码是针对 新井-压力 方案写死的代码，如有其它方案需要遵守 新井-压力 的 命名规则

            if (tempDocument.Range.Bookmarks["审核日期1"].Text.Trim().Length < 1)
            {
                tempDocument.Range.Bookmarks["审核日期1"].Text = "    年   月    日";
            }
            if (tempDocument.Range.Bookmarks["审核日期2"].Text.Trim().Length < 1)
            {
                tempDocument.Range.Bookmarks["审核日期2"].Text = "    年   月    日";
            }


            if (tempDocument.Range.Bookmarks["设计日期"].Text.Trim().Length < 1)
            {
                tempDocument.Range.Bookmarks["设计日期"].Text = DateTime.Now.Year.ToString() + "年" + DateTime.Now.Month.ToString() + "月" + DateTime.Now.Day.ToString() + "日";
                tempDocument.Range.Bookmarks["设计日期1"].Text = DateTime.Now.Year.ToString() + "年" + DateTime.Now.Month.ToString() + "月" + DateTime.Now.Day.ToString() + "日";

            }
            else
            {
                strSql = "select sjrq from tb_fa where jing_id=" + strJingId;
                DateTime time = DateTime.Parse(tempDocument.Range.Bookmarks["设计日期"].Text.Trim());
                tempDocument.Range.Bookmarks["设计日期"].Text = time.Year.ToString() + "年" + time.Month.ToString() + "月" + time.Day.ToString() + "日";//time.ToString("yyyy年mm月dd日");
                tempDocument.Range.Bookmarks["设计日期1"].Text = time.Year.ToString() + "年" + time.Month.ToString() + "月" + time.Day.ToString() + "日";
            }
            HttpCookie cookie = Request.Cookies["user"];
            string strSjr = Server.UrlDecode(cookie.Value).Replace("name=", "");
            Shape shape = new Shape(tempDocument, ShapeType.Image);
            shape.ImageData.SetImage(Server.MapPath("~/Files/autograph/" + strSjr + ".png"));
            shape.Width = 350;
            shape.Height = 32;
            shape.HorizontalAlignment = HorizontalAlignment.Right; //

            builder.MoveToBookmark("pic");
            builder.InsertNode(shape);
           
            if (tempDocument.Range.Bookmarks["垂直深度"] != null)
                tempDocument.Range.Bookmarks["垂直深度"].Text = tempDocument.Range.Bookmarks["射孔井段2"].Text;//h

            if (tempDocument.Range.Bookmarks["定位短节"] != null && tempDocument.Range.Bookmarks["炮头长度"] != null)
                tempDocument.Range.Bookmarks["定位短节"].Text = (System.Convert.ToSingle(tempDocument.Range.Bookmarks["射孔井段2"].Text) - System.Convert.ToSingle(tempDocument.Range.Bookmarks["炮头长度"].Text)).ToString();//射孔井段
            //销钉计算
            if (tempDocument.Range.Bookmarks["ρ"] != null && tempDocument.Range.Bookmarks["静液柱压力"] != null&&tempDocument.Range.Bookmarks["温度"]!=null)
            {
                float h = System.Convert.ToSingle(tempDocument.Range.Bookmarks["射孔井段2"].Text.Trim());
                tempDocument.Range.Bookmarks["ρ"].Text = tempDocument.Range.Bookmarks["完井液密度"].Text;//ρ
                float p = System.Convert.ToSingle(tempDocument.Range.Bookmarks["完井液密度"].Text.Trim());
                double pgh = p * h * 0.0098;
                tempDocument.Range.Bookmarks["静液柱压力"].Text = String.Format("{0:0.00}", pgh); //ρgh

                int t = (int)(h * 0.035 + 1);//温度
                tempDocument.Range.Bookmarks["温度"].Text = t.ToString();

                //百分率
                string strPerSql = "select tem_value from tb_temperature where tem= " + t.ToString();
                float percent = System.Convert.ToSingle(OracleHelper.Query(strPerSql).Tables[0].Rows[0][0]);
                tempDocument.Range.Bookmarks["百分率"].Text = percent.ToString();

                float jqz = System.Convert.ToSingle(tempDocument.Range.Bookmarks["剪切值"].Text.Trim());//常温下的剪切值
                double Pmax = jqz * 1.045 * (1 - percent / 100);
                tempDocument.Range.Bookmarks["Pmax"].Text = String.Format("{0:0.00}", Pmax);

                double Pmin = jqz * 0.955 * (1 - percent / 100);
                tempDocument.Range.Bookmarks["Pmin"].Text = String.Format("{0:0.00}", Pmin);

                float safe_pre = System.Convert.ToSingle(tempDocument.Range.Bookmarks["安全压力"].Text.Trim());
                double n = (pgh + safe_pre) / Pmin;
                tempDocument.Range.Bookmarks["n"].Text = String.Format("{0:0.00}", n);
                int N = (int)(n + 1);
                tempDocument.Range.Bookmarks["m"].Text = N.ToString();

                //E369*H365-F360
                double Ps = N * Pmax - pgh;
                double Px = N * Pmin - pgh;
                tempDocument.Range.Bookmarks["Ps"].Text = String.Format("{0:0.00}", Ps);//Ps
                tempDocument.Range.Bookmarks["Px"].Text = String.Format("{0:0.00}", Px);//Ps
            }

            string strQx = "102";//在数据为空时设置102
            string strQxBm = tempDocument.Range.Bookmarks["枪型"].Text;
            //提取枪型里面的数字
            if (!string.IsNullOrEmpty(strQxBm))
            {
                strQx = "";
                for (int i = 0; i < strQxBm.Length; i++)
                {
                    if (Char.IsNumber(strQxBm[i]))
                        strQx += strQxBm[i];
                }
            }

            string strYggg = "73";
            string strYgggBm = tempDocument.Range.Bookmarks["油管规格"].Text;
            if (!string.IsNullOrEmpty(strYgggBm))
            {
                strYggg = "";
                for (int i = 0; i < strYgggBm.Length; i++)
                {
                    if (Char.IsNumber(strYgggBm[i]))
                        strYggg += strYgggBm[i];
                    else if (strYgggBm[i] == '*')
                        break;
                }
                strYggg = String.Format("{0:0}", (int)(System.Convert.ToSingle(strYggg) + 0.5));
            }

            tempDocument.Range.Bookmarks["起爆装置接头"].Text = strQx;//Ps
            tempDocument.Range.Bookmarks["枪尾"].Text = strQx;//Ps
            tempDocument.Range.Bookmarks["筛管"].Text = strYggg;//Ps
            tempDocument.Range.Bookmarks["油管接箍"].Text = strYggg;//Ps
            tempDocument.Range.Bookmarks["油管短节"].Text = strYggg;//Ps

            string strPath = Server.MapPath("~/tempDoc/") + DateTime.Now.ToString("MM_dd_hh_mm_ss") + ".doc";
            tempDocument.Save(strPath, SaveFormat.Doc);
            FileInfo info = new FileInfo(strPath);
            return info.Name;

        }

        void FillTable(System.Data.DataTable dt, string pkValue)
        {
            foreach (DataRow dr in dt.Rows)
            {
                string strFormula = dr["FORMULA"].ToString();
                if (string.IsNullOrEmpty(strFormula.Trim()))
                {
                    string name = dr["NAME"].ToString();
                    string text = dr["TEXT"].ToString();
                    string tablename = dr["TABLENAME"].ToString();
                    string fieldname = dr["FIELDNAME"].ToString();
                    string pkfield = dr["PKFIELD"].ToString();
                    string strSql = "select " + fieldname + " from " + tablename + " where " + pkfield + "='" + pkValue + "' ";
                    System.Data.DataTable tempDT = OracleHelper.Query(strSql).Tables[0];
                    string strValue = " ";
                    if(tempDT.Rows.Count>0)
                        strValue = System.Convert.IsDBNull(tempDT.Rows[0][0]) ? "" : tempDT.Rows[0][0].ToString();
                    
                    dr["VALUE"] = strValue;
                }
                else
                {
                    System.Data.DataTable dtFormula = OracleHelper.Query("select * from tb_formula where name='" + strFormula + "' ").Tables[0];
                    string pktable = dtFormula.Rows[0]["PKTABLENAME"].ToString();
                    string pkfield = dtFormula.Rows[0]["PKFIELDNAME"].ToString();
                    string Content = dtFormula.Rows[0]["VALUE"].ToString();

                    System.Data.DataTable dtVars = OracleHelper.Query("select * from tb_formula_var where name ='" + strFormula + "' ").Tables[0];
                    foreach (DataRow drVar in dtVars.Rows)
                    {
                        string varName = drVar["BKNAME"].ToString();
                        string strTable = drVar["TABLENAME"].ToString();
                        string strField = drVar["FIELDNAME"].ToString();
                        string strpkField = drVar["PKFIELDNAME"].ToString();
                        string strVarValue = OracleHelper.Query(" select " + strField + " from " + strTable + " where " + strpkField + "='" + pkValue + "' ").Tables[0].Rows[0][0].ToString();
                        Content = Content.Replace(varName, strVarValue);
                    }
                    CodeDriver driver = new CodeDriver();
                    bool isError;
                    string strResult = driver.ComplileAndRun(Content, out isError);//调用执行代码的类
                    dr["VALUE"] = strResult;
                }
            }
            dt.AcceptChanges();

        }

        public string insertNewSchemeToFtp()
        {
            FtpWeb ftp = new FtpWeb();
            try
            {
                string strTemplate_id = Request.QueryString["tempalteid"];//模板ID
                string strJing_id = Request.QueryString["id"];//
                string uploadpath = Server.MapPath(@"~/FileUpLoad/");
                if (!System.IO.Directory.Exists(uploadpath))
                {
                    System.IO.Directory.CreateDirectory(uploadpath);
                }
                System.Web.HttpFileCollection filesupload = Request.Files;
                System.Web.HttpPostedFile file;
                string key = filesupload.GetKey(0).ToLower();
                int i = 0;
                while (i <= filesupload.Count && "editfile" != key)
                {
                    i++;
                    key = filesupload.GetKey(i).ToString();
                }
                if ("editfile" == key)
                {
                    file = filesupload[i];
                    string errorMsg="";
                   // ftp.Upload(file,strJing_id+'_'+strTemplate_id+".doc",out errorMsg);
                    
                  //  string strTemplate_id = Request.QueryString["tempalteid"];//模板ID
                  //  string strJing_id = Request.QueryString["id"];//模板ID
                    // string filePath = Request.QueryString["name"];
                    if (file.ContentLength > 1)
                    {
                        byte[] wordDoc = new byte[file.ContentLength];
                        file.InputStream.Read(wordDoc, 0, (int)file.ContentLength);

                        string strSql = "select * from  tb_fa  where JING_ID=" + strJing_id; // +" and tmp_id= " + strTemplate_id;
                        System.Data.DataTable dt = OracleHelper.Query(strSql).Tables[0];
                        if (dt.Rows.Count > 0)
                        {
                            //strSql = "update tb_fa set word_doc =:word_doc, shehe=:shenhe where JING_ID=" + strJing_id + " and tmp_id= " + strTemplate_id;
                            //OracleParameter p_shenhe = new OracleParameter(":shenhe", OracleType.Number);
                            //OracleParameter p = new OracleParameter(":word_doc", OracleType.Blob);
                            //p.Value = wordDoc;
                            //p_shenhe.Value = 2;
                            //OracleParameter[] op = {p_shenhe, p };
                            //OracleHelper.ExecuteNonQuery(CommandType.Text, strSql, op);
                            strSql = " delete from tb_fa where jing_id= " +strJing_id ;//   insert into tb_fa(jing_id,tmp_id,sjr,sjrq,word_doc,sjr_code,shenhe) values (:jing_id,:tmp_id,:sjr,:sjrq,:word_doc,:sjr_code,:shenhe )";
                            OracleHelper.ExecuteSql(strSql);
                        }
                        
                        
                            strSql = "insert into tb_fa(jing_id,tmp_id,sjr,sjrq,word_doc,sjr_code,shenhe) values (:jing_id,:tmp_id,:sjr,:sjrq,:word_doc,:sjr_code,:shenhe )";

                            OracleParameter p = new OracleParameter(":jing_id", OracleType.Number);
                            OracleParameter p1 = new OracleParameter(":tmp_id", OracleType.Number);
                            OracleParameter p2 = new OracleParameter(":sjr", OracleType.VarChar);
                            OracleParameter p3 = new OracleParameter(":sjrq", OracleType.DateTime);
                            OracleParameter p4 = new OracleParameter(":word_doc", OracleType.Blob);
                            OracleParameter p5 = new OracleParameter(":sjr_code", OracleType.VarChar);
                            OracleParameter p6 = new OracleParameter(":shenhe", OracleType.Number);
                            HttpCookie cookie = Request.Cookies["user"];
                            p.Value = System.Convert.ToInt32(strJing_id);
                            p1.Value = System.Convert.ToInt32(strTemplate_id);
                            p2.Value = Server.UrlDecode(cookie.Value).Replace("name=", "");
                            p3.Value = DateTime.Now;
                            p4.Value = wordDoc;
                            p5.Value = DQYT.HTDZ.Web.Globals.CurrentUserInfo.USE_CODE;
                            p6.Value = 2;
                            OracleParameter[] op = { p, p1, p2, p3, p4, p5,p6 };
                            OracleHelper.ExecuteNonQuery(CommandType.Text, strSql, op);

                        
                    
                    }
                    
                    

                    return "操作成功！";
                }
                else
                {
                    return "尚未找到操作的文件！";
                }
            }
            catch (Exception ex)
            {
                return ex.Message.ToString();
            }
        }


        public string insertNewSchemeToOracle()
        {
            try
            {
                //string uploadpath = Server.MapPath(@"~/FileUpLoad/");
                //if (!System.IO.Directory.Exists(uploadpath))
                //{
                //    System.IO.Directory.CreateDirectory(uploadpath);
                //}
                System.Web.HttpFileCollection filesupload = Request.Files;
                System.Web.HttpPostedFile file;
                string key = filesupload.GetKey(0).ToLower();
                int i = 0;
                while (i <= filesupload.Count && "editfile" != key)
                {
                    i++;
                    key = filesupload.GetKey(i).ToString();
                }
                if ("editfile" == key)
                {
                    file = filesupload[i];

                    string strTemplate_id = Request.QueryString["tempalteid"];//模板ID
                    string strJing_id = Request.QueryString["id"];//模板ID


                    // string filePath = Request.QueryString["name"];
                    if (file.ContentLength > 1)
                    {
                        byte[] wordDoc = new byte[file.ContentLength];
                        file.InputStream.Read(wordDoc, 0, (int)file.ContentLength);
                        //string strSql;
                        string strSql = "select * from  tb_fa  where JING_ID=" + strJing_id;// +" and tmp_id= " + strTemplate_id;
                        DataTable dt = OracleHelper.Query(strSql).Tables[0];
                        if (dt.Rows.Count > 0)
                        {
                            if (Request.QueryString["audit"] == "1")//审核时保存
                            {
                                strSql = "update tb_fa set word_doc =:word_doc  where JING_ID=" + strJing_id;// +" and tmp_id= " + strTemplate_id;
                                OracleParameter p = new OracleParameter(":word_doc", OracleType.Blob);
                                //OracleParameter p1 = new OracleParameter(":shenhe", OracleType.Number);
                                //OracleParameter p2 = new OracleParameter(":sjr", OracleType.VarChar);
                                //OracleParameter p3 = new OracleParameter(":sjr_code", OracleType.VarChar);
                                p.Value = wordDoc;
                                //p1.Value = 0;
                                //p2.Value = DQYT.HTDZ.Web.Globals.CurrentUserInfo.USE_NAME;
                                //p3.Value = DQYT.HTDZ.Web.Globals.CurrentUserInfo.USE_CODE;

                                OracleParameter[] op = { p };
                                OracleHelper.ExecuteNonQuery(CommandType.Text, strSql, op);
                            }
                            else//设计时保存
                            {

                                strSql = " delete from tb_fa where jing_id= " + strJing_id;//   insert into tb_fa(jing_id,tmp_id,sjr,sjrq,word_doc,sjr_code,shenhe) values (:jing_id,:tmp_id,:sjr,:sjrq,:word_doc,:sjr_code,:shenhe )";
                                OracleHelper.ExecuteSql(strSql);


                                strSql = "insert into tb_fa(jing_id,tmp_id,sjr,sjrq,word_doc,sjr_code,shenhe) values (:jing_id,:tmp_id,:sjr,:sjrq,:word_doc,:sjr_code ,:shenhe)";

                                OracleParameter p = new OracleParameter(":jing_id", OracleType.Number);
                                OracleParameter p1 = new OracleParameter(":tmp_id", OracleType.Number);
                                OracleParameter p2 = new OracleParameter(":sjr", OracleType.VarChar);
                                OracleParameter p3 = new OracleParameter(":sjrq", OracleType.DateTime);
                                OracleParameter p4 = new OracleParameter(":word_doc", OracleType.Blob);
                                OracleParameter p5 = new OracleParameter(":sjr_code", OracleType.VarChar);
                                OracleParameter p6 = new OracleParameter(":shenhe", OracleType.Number);
                                HttpCookie cookie = Request.Cookies["user"];
                                p.Value = System.Convert.ToInt32(strJing_id);
                                p1.Value = System.Convert.ToInt32(strTemplate_id);
                                p2.Value = Server.UrlDecode(cookie.Value).Replace("name=", "");
                                p3.Value = DateTime.Now;
                                p4.Value = wordDoc;
                                p5.Value = DQYT.HTDZ.Web.Globals.CurrentUserInfo.USE_CODE;
                                p6.Value = 0;
                                OracleParameter[] op = { p, p1, p2, p3, p4, p5, p6 };
                                OracleHelper.ExecuteNonQuery(CommandType.Text, strSql, op);
                            
                            }
                        }

                        else//设计时保存
                        {
                            strSql = "insert into tb_fa(jing_id,tmp_id,sjr,sjrq,word_doc,sjr_code,shenhe) values (:jing_id,:tmp_id,:sjr,:sjrq,:word_doc,:sjr_code ,:shenhe)";

                            OracleParameter p = new OracleParameter(":jing_id", OracleType.Number);
                            OracleParameter p1 = new OracleParameter(":tmp_id", OracleType.Number);
                            OracleParameter p2 = new OracleParameter(":sjr", OracleType.VarChar);
                            OracleParameter p3 = new OracleParameter(":sjrq", OracleType.DateTime);
                            OracleParameter p4 = new OracleParameter(":word_doc", OracleType.Blob);
                            OracleParameter p5 = new OracleParameter(":sjr_code", OracleType.VarChar);
                            OracleParameter p6 = new OracleParameter(":shenhe", OracleType.Number);
                            HttpCookie cookie = Request.Cookies["user"];
                            p.Value = System.Convert.ToInt32(strJing_id);
                            p1.Value = System.Convert.ToInt32(strTemplate_id);
                            p2.Value = Server.UrlDecode(cookie.Value).Replace("name=", "");
                            p3.Value = DateTime.Now;
                            p4.Value = wordDoc;
                            p5.Value = DQYT.HTDZ.Web.Globals.CurrentUserInfo.USE_CODE;
                            p6.Value = 0;
                            OracleParameter[] op = { p, p1, p2, p3, p4, p5, p6 };
                            OracleHelper.ExecuteNonQuery(CommandType.Text, strSql, op);
                        }

                        

                    }



                    return "操作成功！";
                }
                else
                {
                    return "尚未找到操作的文件！";
                }
            }
            catch (Exception ex)
            {
                return ex.Message.ToString();
            }
        }

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            string strTemplate_id = Request.QueryString["tempalteid"];//模板ID
            string strJing_id = Request.QueryString["id"];//模板ID
            string filePath = Request.QueryString["name"];
            if (filePath != null)
            {
                filePath = Server.MapPath("~/")+"tempDoc\\" + filePath;
                FileStream stream = new FileStream(filePath,FileMode.Open);
                byte[] wordDoc = new byte[stream.Length];
                stream.Read(wordDoc,0,(int)stream.Length);

                string strSql = "select * from  tb_fa  where JING_ID=" + strJing_id + " and tmp_id= " + strTemplate_id;
                System.Data.DataTable dt = OracleHelper.Query(strSql).Tables[0];
                if (dt.Rows.Count > 0)
                {
                    //strSql = "update tb_fa set word_doc =:word_doc where JING_ID=" + strJing_id + " and tmp_id= " + strTemplate_id;

                    //OracleParameter p = new OracleParameter(":word_doc", OracleType.Blob);
                    //p.Value = wordDoc;
                    //OracleParameter[] op = { p };
                    //OracleHelper.ExecuteNonQuery(CommandType.Text, strSql, op);


                    strSql = " delete from tb_fa where jing_id= " + strJing_id;//   insert into tb_fa(jing_id,tmp_id,sjr,sjrq,word_doc,sjr_code,shenhe) values (:jing_id,:tmp_id,:sjr,:sjrq,:word_doc,:sjr_code,:shenhe )";
                    OracleHelper.ExecuteSql(strSql);

                }



                //else
                //{
                //        strSql = "insert into tb_fa(jing_id,tmp_id,word_doc) values (:jing_id,:tmp_id,:word_doc )";


                //        OracleParameter p = new OracleParameter(":jing_id", OracleType.Number);
                //        OracleParameter p1 = new OracleParameter(":tmp_id", OracleType.Number);
                //        OracleParameter p2 = new OracleParameter(":word_doc", OracleType.Blob);

                //        p.Value = System.Convert.ToInt32(strJing_id);
                //        p1.Value = System.Convert.ToInt32(strTemplate_id);
                //        p2.Value = wordDoc;
                //        OracleParameter[] op = { p,p1,p2 };
                //        OracleHelper.ExecuteNonQuery(CommandType.Text, strSql, op);

                //  }



                strSql = "insert into tb_fa(jing_id,tmp_id,sjr,sjrq,word_doc,sjr_code,shenhe) values (:jing_id,:tmp_id,:sjr,:sjrq,:word_doc,:sjr_code,:shenhe )";

                OracleParameter p = new OracleParameter(":jing_id", OracleType.Number);
                OracleParameter p1 = new OracleParameter(":tmp_id", OracleType.Number);
                OracleParameter p2 = new OracleParameter(":sjr", OracleType.VarChar);
                OracleParameter p3 = new OracleParameter(":sjrq", OracleType.DateTime);
                OracleParameter p4 = new OracleParameter(":word_doc", OracleType.Blob);
                OracleParameter p5 = new OracleParameter(":sjr_code", OracleType.VarChar);
                OracleParameter p6 = new OracleParameter(":shenhe", OracleType.Number);
                HttpCookie cookie = Request.Cookies["user"];
                p.Value = System.Convert.ToInt32(strJing_id);
                p1.Value = System.Convert.ToInt32(strTemplate_id);
                p2.Value = Server.UrlDecode(cookie.Value).Replace("name=", "");
                p3.Value = DateTime.Now;
                p4.Value = wordDoc;
                p5.Value = DQYT.HTDZ.Web.Globals.CurrentUserInfo.USE_CODE;
                p6.Value = 2;
                OracleParameter[] op = { p, p1, p2, p3, p4, p5, p6 };
                OracleHelper.ExecuteNonQuery(CommandType.Text, strSql, op);

            }
        }

        /// <summary>
        /// 读取 Oracle Blob 到文件中。
        /// </summary>
        /// <param name="idData">id 值</param>
        /// <param name="fileName">文件名</param>
        /// <param name="id">id 键</param>
        /// <param name="blob">blob 键</param>
        /// <param name="tableName">表名</param>
        public MemoryStream ReadBlob(int idData, string id, string blob, string tableName)
        {
            OracleConnection conn;
            string connString = ConfigurationManager.AppSettings["ConnectionString"];
            using (conn = new OracleConnection(connString))
            {
                try
                {
                    conn.Open();
                    OracleCommand cmd = conn.CreateCommand();

                    // 利用事务处理（必须）
                    OracleTransaction transaction = cmd.Connection.BeginTransaction();
                    cmd.Transaction = transaction;

                    // 获得 OracleLob 指针
                    string sql = "select " + blob + " from " + tableName + " where " + id + " = " + idData;
                    cmd.CommandText = sql;
                    OracleDataReader dr = cmd.ExecuteReader();
                    dr.Read();
                    OracleLob tempLob = dr.GetOracleLob(0);
                    dr.Close();

                    // 读取 BLOB 中数据，写入到文件中
                    MemoryStream fs = new MemoryStream();
                    //FileStream fs = new FileStream(fileName, FileMode.Create);
                    int length = 1048576;
                    byte[] Buffer = new byte[length];
                    int i;
                    while ((i = tempLob.Read(Buffer, 0, length)) > 0)
                    {
                        fs.Write(Buffer, 0, i);
                    }
                   // fs.Close();
                    tempLob.Clone();
                    cmd.Parameters.Clear();

                    // 提交事务
                    transaction.Commit();
                    return fs;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        protected void save_Click(object sender, EventArgs e)
        {
            //var tmp_id = escape(getParam("tempalteid"));
            //var jing_id = escape(getParam("id"));
            string tmp_id = Context.Request.QueryString["tempalteid"].ToString();
            string jing_id = Context.Request.QueryString["id"].ToString();
        }



    }




   
}